﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
   public class 商品消费查询DAL
    {
       public static SqlDataReader Query(Model_ConsumerBill_Tables_ConsumerDetail_Vips m)
       {
           List<SqlParameter> list =new List<SqlParameter>();
           string sql = @"select TableName,ProductName,CDPrice,CDAmount,CDMoney,CDSale,CDMoney-CDSale,CDDate from dbo.ConsumerBill c inner join
            dbo.Tables t on  c.TableID=t.TableID inner join dbo.ConsumerDetail d on D.CBID=C.CBID  inner join  dbo.Products p
            on p.ProductID=d.ProdcutID where 1 = 1 ";
           if (!string.IsNullOrEmpty(m.CBStartDate) && m.CBStartDate !="")
           {
               sql += " and CBStartDate >=@CBStartDate ";
           list.Add(new SqlParameter("@CBStartDate",DateTime.Parse(m.CBStartDate)));

           }
           if (!string.IsNullOrEmpty(m.CBEndDate) && m.CBEndDate != "")
           {
               sql += " and CBEndDate < @CBEndDate";
               list.Add(new SqlParameter("@CBEndDate", DateTime.Parse(m.CBEndDate)));
           }
           return DBHelper.Zhilianshi(sql,list.ToArray());
       }
       public static SqlDataReader Query2(Model_ConsumerBill_Tables_ConsumerDetail_Vips m)
       {
           List<SqlParameter> list = new List<SqlParameter>();
           string sql = @"select TableName,ProductName,CDPrice,CDAmount,CDMoney,CDSale,CDMoney-CDSale,CDDate from dbo.ConsumerBill c inner join
            dbo.Tables t on  c.TableID=t.TableID inner join dbo.ConsumerDetail d on D.CBID=C.CBID  inner join  dbo.Products p
            on p.ProductID=d.ProdcutID where 1 = 1 ";
           if (m.TableName.Equals("1"))
           {
               if (!string.IsNullOrEmpty(m.ProductName) && m.ProductName != "")
               {
                   sql += " and TableName like '%'+@TableName+'%' ";
                   list.Add(new SqlParameter("@TableName", m.ProductName));

               }
           }
           if (m.TableName.Equals("2"))
           {
               if (!string.IsNullOrEmpty(m.ProductName) && m.ProductName != "")
               {
                   sql += " and ProductName like '%'+@ProductName+'%' ";
                   list.Add(new SqlParameter("@ProductName", m.ProductName));

               }
           }
           return DBHelper.Zhilianshi(sql, list.ToArray());
       }
    }
}
